package com.slipper.core.poi.excel;

import com.slipper.util.Tool;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import java.io.*;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.regex.Matcher;
import java.util.regex.Pattern;

public class ExcelTemplate implements Closeable {
    private final InputStream inputStream;
    private final Workbook tplWorkbook;
    private final Workbook newWorkbook;

    public ExcelTemplate(InputStream inputStream) throws IOException {
        this.inputStream = inputStream;
        this.tplWorkbook = WorkbookFactory.create(inputStream);
        this.newWorkbook = new XSSFWorkbook();
    }

    public void render(Map<String, Object> data) {
        for (int i=0; i<this.tplWorkbook.getNumberOfSheets(); i++) {
            Sheet tplSheet = this.tplWorkbook.getSheetAt(i);
            if (tplSheet.getLastRowNum() < 0) continue;

            render(this.newWorkbook.createSheet(tplSheet.getSheetName()), tplSheet, data);
        }
    }

    private void render(Sheet newSheet, Sheet tplSheet, Map<String, Object> data) {
        newSheet.setHorizontallyCenter(tplSheet.getHorizontallyCenter());
        newSheet.setVerticallyCenter(tplSheet.getVerticallyCenter());
        newSheet.setDefaultColumnWidth(tplSheet.getDefaultColumnWidth());

        for (int i = 0; i <= tplSheet.getLastRowNum(); i++) {
            Row tplRow = tplSheet.getRow(i);
            if (tplRow == null) {
                newSheet.createRow(newSheet.getLastRowNum() + 1);
                continue;
            }

            String loopKey = getLoopKey(tplRow);
            if (Tool.isNotBlank(loopKey)) {
                render(newSheet, tplRow, loopKey, data);
            } else {
                render(newSheet.createRow(newSheet.getLastRowNum() + 1), tplRow, data);
            }
        }

        // 设置列宽
        int columnCount = tplSheet.getRow(0).getLastCellNum();
        int defaultColumnWidth = tplSheet.getDefaultColumnWidth();
        for (int j=0; j<columnCount; j++) {
            if (defaultColumnWidth * 256 != tplSheet.getColumnWidth(j)) {
                newSheet.setColumnWidth(j, tplSheet.getColumnWidth(j));
            }
        }

        // 处理合并的单元格
        for (int k=0; k<tplSheet.getMergedRegions().size(); k++) {
            CellRangeAddress mergedRegion = tplSheet.getMergedRegion(k);
            newSheet.addMergedRegion(mergedRegion);
        }
    }

    @SuppressWarnings("unchecked")
    private void render(
            Sheet newSheet,
            Row tplRow,
            String loopKey,
            Map<String, Object> data) {
        List<Map<String, Object>> loopListData = (List<Map<String, Object>>) data.get(loopKey);
        if (loopListData == null || loopListData.isEmpty()) return;

        int baseRow = newSheet.getLastRowNum() + 1;
        Map<Integer, CellStyle> cellStyleMap = new HashMap<>();
        loopListData.forEach(loopItemData -> {
            Row newRow = newSheet.createRow(newSheet.getLastRowNum() + 1);
            newRow.setHeight(tplRow.getHeight());
            newRow.setRowStyle(tplRow.getRowStyle());

            for (int i=0; i<=tplRow.getLastCellNum(); i++) {
                Cell tplCell = tplRow.getCell(i);
                if (tplCell == null) {
                    newRow.createCell(i);
                    continue;
                }

                Cell newCell = newRow.createCell(i);
                CellStyle cellStyle = cellStyleMap.get(i);
                if (cellStyle == null) {
                    cellStyle = this.newWorkbook.createCellStyle();
                    cellStyle.cloneStyleFrom(tplCell.getCellStyle());
                    cellStyleMap.put(i, cellStyle);
                }
                newCell.setCellStyle(cellStyle);
                render(newCell, tplCell, data, loopKey, loopItemData);
            }
        });

        // 合并单元格
        List<List<Integer>> mergeList = (List<List<Integer>>) data.get("merge_" + loopKey);
        if (mergeList != null && !mergeList.isEmpty()) {
            mergeList.forEach(item -> {
                CellRangeAddress mergedRegion = new CellRangeAddress(
                        baseRow + item.get(0),
                        baseRow + item.get(1),
                        item.get(2),
                        item.get(3));
                newSheet.addMergedRegion(mergedRegion);
            });
        }
    }

    private void render(Row newRow, Row tplRow, Map<String, Object> data) {
        newRow.setHeight(tplRow.getHeight());
        newRow.setRowStyle(tplRow.getRowStyle());

        for (int i=0; i<=tplRow.getLastCellNum(); i++) {
            Cell tplCell = tplRow.getCell(i);
            if (tplCell == null) {
                newRow.createCell(i);
                continue;
            }

            Cell newCell = newRow.createCell(i);
            CellStyle cellStyle = this.newWorkbook.createCellStyle();
            cellStyle.cloneStyleFrom(tplCell.getCellStyle());
            newCell.setCellStyle(cellStyle);
            render(newCell, tplCell, data);
        }
    }

    private void render(Cell newCell, Cell tplCell, Map<String, Object> data) {
        render(newCell, tplCell, data, null, null);
    }

    private void render(
            Cell newCell,
            Cell tplCell,
            Map<String, Object> data,
            String loopKey,
            Map<String, Object> loopItemData) {
        switch (tplCell.getCellType()) {
            case STRING -> {
                String value = parseValue(tplCell.getStringCellValue(), data, loopKey, loopItemData);
                if (Tool.isNumber(value)) {
                    newCell.setCellValue(value.indexOf('.') >= 0 ? Double.parseDouble(value) : Integer.parseInt(value));
                } else {
                    newCell.setCellValue(value);
                }
            }
            case NUMERIC -> {
                if (DateUtil.isCellDateFormatted(tplCell)) {
                    newCell.setCellValue(tplCell.getDateCellValue());
                } else {
                    newCell.setCellValue(tplCell.getNumericCellValue());
                }
            }
            case FORMULA -> newCell.setCellFormula(tplCell.getCellFormula());
            case BLANK, _NONE -> newCell.setCellValue("");
            case BOOLEAN -> newCell.setCellValue(tplCell.getBooleanCellValue());
            case ERROR -> newCell.setCellValue(tplCell.getErrorCellValue());
        }
    }

    private String parseValue(
            String tplValue,
            Map<String, Object> data,
            String loopKey,
            Map<String, Object> loopItemData) {
        if (Tool.isBlank(tplValue)) return "";

        // 替换 {{}} 里面的值
        Pattern pattern = Pattern.compile("\\{\\{(.*?)}}");
        Matcher matcher = pattern.matcher(tplValue);
        String result = tplValue;
        while (matcher.find()) {
            Object keyValue = data.get(matcher.group(1).trim());
            result = result.replace(matcher.group(), keyValue == null ? "" : keyValue.toString());
        }

        if (Tool.isBlank(loopKey)) return result;

        // 替换 {} 里面的值
        pattern = Pattern.compile("\\{(.*?)}");
        matcher = pattern.matcher(tplValue);
        while (matcher.find()) {
            Object keyValue = loopItemData.get(matcher.group(1).replace("t.", "").trim());
            result = result.replace(matcher.group(), keyValue == null ? "" : keyValue.toString());
        }

        // 删除 {{loopKey 以及 }} 占位符
        result = result.replaceAll("\\{\\{\\s*" + loopKey + " |}}", "");

        // 替换循环数据值 t. 开头的值
        String[] items = result.split(" ");
        for (String item : items) {
            if (item != null && item.trim().startsWith("t.")) {
                Object keyValue = loopItemData.get(item.trim().replace("t.", "").trim());
                result = result.replace(item.trim(), keyValue == null ? "" : keyValue.toString());
            }
        }

        return result;
    }

    private String getLoopKey(Row row) {
        for (int i=0; i<row.getLastCellNum(); i++) {
            Cell cell = row.getCell(i);
            if (cell == null || cell.getCellType() != CellType.STRING) continue;

            String cellValue = cell.getStringCellValue().replaceAll("\\{\\{.*}}", "");
            if (cellValue.contains("{{")) {
                String keyName = cellValue.substring(cellValue.indexOf("{{") + 2).trim();
                return keyName.split(" ")[0];
            }
        }
        return null;
    }

    public void write(File file) throws IOException {
        FileOutputStream outputStream = new FileOutputStream(file);
        this.newWorkbook.write(outputStream);
    }

    public void write(OutputStream outputStream) throws IOException {
        this.newWorkbook.write(outputStream);
    }

    @Override
    public void close() throws IOException {
        this.inputStream.close();
        this.tplWorkbook.close();
        this.newWorkbook.close();
    }

    public static void main(String[] args) {
        String loopKey = "dataList";
        String result = "{t.姓名} t.名称}}";
        result = result.replaceAll("\\{\\{\\s*" + loopKey + " |}}", "");
        System.out.println(result);
    }
}
